﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office;
using System.Web;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using FCModel;

namespace MiscLib
{
    public class ExcelExport
    {
        //public static string Path = Server.MapPath("~/Excel/");
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        private static void Kill_Excel(Microsoft.Office.Interop.Excel.Application excel)
        {
            IntPtr t = new IntPtr(excel.Hwnd);   //得到这个句柄，具体作用是得到这块内存入口 
            int k = 0;
            GetWindowThreadProcessId(t, out k);   //得到本进程唯一标志k
            if (k == 0)    //由于权限不足 获取不到PID时
            {
                System.Diagnostics.Process[] p = System.Diagnostics.Process.GetProcessesByName("Excel");
                foreach (Process process in p)
                {
                    process.Kill();
                }
            }
            else
            {
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);   //得到对进程k的引用
                p.Kill();     //关闭进程k
            }
        }


        public static void WriteLog(string path, string LogText)
        {
            string strLogFilePath = path + "FileConverter.log";
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            using (StreamWriter sw = new StreamWriter(strLogFilePath, true))
            {
                sw.WriteLine(DateTime.Now.ToString() + ": " + LogText);
            }
        }

        public static void OutputExcel(DataView dv, string path,string partNo, string FileName, HttpResponse Response, HttpServerUtility Server,string logPath)
        {

            #region excel 文件生成
            string FullFileName = path + FileName + ".xlsx";
            GC.Collect();
            Application excel = new Application();
            //excel.Visible = true;
            _Workbook xBk;
            _Worksheet xSt;
            try
            {
               // WriteLog(logPath, "进入生成excel4");
                int excelColNum = dv.ToTable().Columns.Count;
                if (excelColNum < 21)
                    excelColNum = 21;
                xBk = excel.Workbooks.Add(true);
                xSt = (_Worksheet)xBk.ActiveSheet;
                //取消网格线               
                excel.ActiveWindow.DisplayGridlines = false;
                //设置显示比例
                excel.ActiveWindow.Zoom = 70;
                //设置字体
                #region 表头
                Range rg = xSt.Range[excel.Cells[1, 1], excel.Cells[100, 100]];
                rg.Font.Size = 14;
                rg.Font.Name = "Times New Roman";
                //设置行高
                rg.RowHeight = 33;
                //设置第2列宽
                rg = xSt.Range[excel.Cells[1, 2], excel.Cells[1, 2]];
                rg.ColumnWidth = 11.13;
                //第1行
                rg = xSt.Range[excel.Cells[1, 1], excel.Cells[1, 1]];
                rg.Select();

                float PicLeft, PicTop;
                PicLeft = Convert.ToSingle(rg.Left);
                PicTop = Convert.ToSingle(rg.Top);
                string picturePath = Server.MapPath("../../") + @"Images\KaifaLogon.png";
                xSt.Shapes.AddPicture(picturePath, Microsoft.Office.Core.MsoTriState.msoFalse, 
                    Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, 500, 70);

                //第2行
                excel.Cells[2, 11] = "INCOMING MATERIAL INSPECTION REPORT";
                rg = xSt.Range[excel.Cells[2, 11], excel.Cells[2, 11]];
                rg.Font.Bold = true;
                rg.Font.Size = 20;
                rg.Font.Name = "宋体";
                //第3行
                excel.Cells[3, 13] = "IQC AUDIT RESULTS";
                rg = xSt.Range[excel.Cells[3, 13], excel.Cells[3, 13]];
                rg.Font.Size = 20;
                rg.Font.Name = "宋体";
                //第4行
                excel.Cells[4, excelColNum - 1] = "Dept.: IQC/SQE";
                rg = xSt.Range[excel.Cells[4, excelColNum - 1], excel.Cells[4, excelColNum - 1]];
                rg.Font.Size = 20;
                rg.Font.Name = "Times New Roman";
                //第5行
                //第6行
                rg = xSt.Range[excel.Cells[5, 1], excel.Cells[5, 4]];
                rg.Merge(0);
                excel.Cells[5, 1] = "PART NUMBER";
                rg = xSt.Range[excel.Cells[6, 1], excel.Cells[6, 4]];
                rg.Merge(0);

                excel.Cells[5, 5] = "REV";

                rg = xSt.Range[excel.Cells[5, 6], excel.Cells[5, 9]];
                rg.Merge(0);
                excel.Cells[5, 6] = "PART NAME";
                rg = xSt.Range[excel.Cells[6, 6], excel.Cells[6, 9]];
                rg.Merge(0);
                excel.Cells[6, 1] = partNo;
  
                rg = xSt.Range[excel.Cells[5, 10], excel.Cells[5, 12]];
                rg.Merge(0);
                excel.Cells[5, 10] = "VENDOR";
                rg = xSt.Range[excel.Cells[6, 10], excel.Cells[6, 12]];
                rg.Merge(0);

                rg = xSt.Range[excel.Cells[5, 13], excel.Cells[5, 14]];
                rg.Merge(0);
                excel.Cells[5, 13] = "G.R.#";
                rg = xSt.Range[excel.Cells[6, 13], excel.Cells[6, 14]];
                rg.Merge(0);

                rg = xSt.Range[excel.Cells[5, 15], excel.Cells[5, 16]];
                rg.Merge(0);
                excel.Cells[5, 15] = "LOT QTY";
                rg = xSt.Range[excel.Cells[6, 15], excel.Cells[6, 16]];
                rg.Merge(0);

                rg = xSt.Range[excel.Cells[5, 17], excel.Cells[5, 18]];
                rg.Merge(0);
                excel.Cells[5, 17] = "INSPECTOR";
                rg = xSt.Range[excel.Cells[6, 17], excel.Cells[6, 18]];
                rg.Merge(0);

                rg = xSt.Range[excel.Cells[5, 19], excel.Cells[5, 20]];
                rg.Merge(0);
                excel.Cells[5, 19] = "DATE";
                rg = xSt.Range[excel.Cells[6, 19], excel.Cells[6, 20]];
                rg.Merge(0);

                rg = xSt.Range[excel.Cells[5, 21], excel.Cells[5, excelColNum]];
                rg.Merge(0);
                excel.Cells[5, 21] = "INSTRUMENT USED";
                rg = xSt.Range[excel.Cells[6, 21], excel.Cells[6, excelColNum]];
                rg.Merge(0);                
                //第7行
                rg = xSt.Range[excel.Cells[7, 1], excel.Cells[7, excelColNum]];
                rg.Merge(0);
                excel.Cells[7, 1] = "TYPE OF AUDIT:(NORMAL/SKIP/MONTHLY)";               
                //第8行
                rg = xSt.Range[excel.Cells[8, 1], excel.Cells[8, excelColNum]];
                rg.Merge(0);            
                //第9行
                excel.Cells[9, 1] = "ITEM";
                excel.Cells[9, 2] = "Parameter";               
                rg = xSt.Range[excel.Cells[9, 6], excel.Cells[9, excelColNum]];
                rg.Merge(0);
                excel.Cells[9, 6] = "MEASUREMENT RESULT";
                rg = xSt.Range[excel.Cells[9, 3], excel.Cells[9, 5]];
                rg.Merge(0);
                excel.Cells[9, 3] = "SPEC.";
                //WriteLog(logPath, "进入生成excel5");

                //设置6-10行的对齐
                rg = xSt.Range[excel.Cells[5, 1], excel.Cells[10, excelColNum]];
                rg.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rg.VerticalAlignment = XlVAlign.xlVAlignBottom;
                rg = xSt.Range[excel.Cells[7, 1], excel.Cells[7, 1]];
                rg.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                #endregion
                #region 内容
                //第10行
                int rowIndex = 10;
                int colIndex = 0;
                foreach (DataColumn col in dv.Table.Columns)
                {
                    colIndex++;
                    if (colIndex == 1)
                    {
                        excel.Cells[10, colIndex] = "#";
                        continue;
                    }
                    if (colIndex == 2)
                    {
                        excel.Cells[10, colIndex] = "";
                        continue;
                    }
                    excel.Cells[10, colIndex] = col.ColumnName;
                    xSt.Range[excel.Cells[10, colIndex], excel.Cells
                    [10, colIndex]].HorizontalAlignment
                    = XlVAlign.xlVAlignCenter;//Set title format as middle 
                }
                //数据部分
                //数据部分单元格一起填充
                rg = xSt.Range[excel.Cells[11, 6], excel.Cells[10 + dv.Count, dv.Table.Columns.Count]];
                rg.NumberFormatLocal = "0.00000_ ";
                rg = xSt.Range[excel.Cells[11, 1], excel.Cells[10 + dv.Count, dv.Table.Columns.Count]];
                object[,] objData = new Object[dv.Count, dv.Table.Columns.Count];
                IList<Limit> listLt = new List<Limit>();
                IList<Limit> listNS = new List<Limit>();
                IList<int> listNotStandard = new List<int>();
                for (int i = 0; i < dv.Count; i++)
                {
                    for (int j = 0; j < dv.ToTable().Columns.Count; j++)
                    {
                        objData[i, j] = dv[i][j];
                        #region 记录在范围之外的值
                        //   dv[i][3]Max    dv[i][4] Min 
                        if (j > 4)
                        {
                            try
                            {
                                if (String.IsNullOrWhiteSpace(dv[i][3].ToString()) || String.IsNullOrWhiteSpace(dv[i][4].ToString()))
                                {
                                    if (!listNotStandard.Contains(10 + 1 + i))
                                        listNotStandard.Add(10 + 1 + i);
                                }
                                else if (Double.Parse(dv[i][4].ToString()) > Double.Parse(dv[i][j].ToString()) || Double.Parse(dv[i][j].ToString()) > Double.Parse(dv[i][3].ToString()))
                                {
                                    listLt.Add(new Limit() { Row = 10 + 1 + i, Column = j + 1 });
                                }
                            }
                            catch { }
                        }
                        #endregion
                    }
                }
                rg.Value2 = objData;
              //  WriteLog(logPath, "进入生成excel7");
                foreach (Limit lt in listLt) //变土黄
                {
                    rg = xSt.Range[excel.Cells[lt.Row, lt.Column], excel.Cells[lt.Row, lt.Column]];      
                    //设置单元格背景颜色
                    rg.Cells.Interior.Pattern = Constants.xlSolid;
                    rg.Cells.Interior.PatternColorIndex = Constants.xlAutomatic;
                    rg.Cells.Interior.Color = 3381759;
                    rg.Cells.Interior.TintAndShade = 0;
                    rg.Cells.Interior.PatternTintAndShade = 0;
                }
                foreach (int row in listNotStandard)//变绿
                {
                    rg = xSt.Range[excel.Cells[row, 1], excel.Cells[row, dv.Table.Columns.Count]];      
                    //设置单元格背景颜色
                    rg.Cells.Interior.Pattern = Constants.xlSolid;
                    rg.Cells.Interior.PatternColorIndex = Constants.xlAutomatic;
                    rg.Cells.Interior.Color=10092441;
                    rg.Cells.Interior.TintAndShade = 0;
                    rg.Cells.Interior.PatternTintAndShade = 0;
                }
                //oSheet.Cells.Columns.AutoFit();
                #endregion
                //设置5-内容行的边框
                rg = xSt.Range[excel.Cells[5, 1], excel.Cells[10 + dv.Count, excelColNum]];
                rg.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                // 
                //footer
                // 
                excel.Cells[11 + dv.Count, 1] = "Form No.: 33FM-A013-033 Rev.02 ";
             //   WriteLog(logPath, "进入生成excel8");
                System.IO.FileInfo fi = new System.IO.FileInfo(FullFileName);
                if (!System.IO.Directory.Exists(fi.DirectoryName))
                    System.IO.Directory.CreateDirectory(fi.DirectoryName);
             //   WriteLog(logPath, "进入生成excel9");

                if (System.IO.File.Exists(FullFileName))
                {
                    File.Delete(FullFileName);
                }
              //  WriteLog(logPath, "进入生成excel10");
                xBk.SaveCopyAs(FullFileName);
                dv = null;
                xBk.Close(false, null, null);
                excel.Quit();
               // Marshal.ReleaseComObject(excel);
                WriteLog(logPath, "进入生成excel11");
   
            }
            finally
            {
               //rg = null;
               //xSt = null;
               //xBk = null;
               //excel = null;
                try
                {
                    Kill_Excel(excel);
                }
                catch { ; }
                GC.Collect();
            }
            #endregion
            WriteLog(logPath, "成功生成excel");
            #region download excel
            DownLoadFile(FullFileName, Response, Server, logPath);

            WriteLog(logPath, "成功下载excel");
            #endregion
        }

        public static void OutputExcelBOM(DataView dv,IList<BOMParameterDao> listSSHead,IList<BOMParameterDao> list, string path, string BomType, string FileName, HttpResponse Response, HttpServerUtility Server, string logPath)
        {
           // FCModel.BOM_Head btt = FCBLL.CommonBLL.getBOM_HeadByBOMType(BOMType);
            #region excel 文件生成
            string FullFileName = path + FileName + ".xlsx";
            GC.Collect();
            Application excel = new Application();
            //excel.Visible = true;
            _Workbook xBk;
            _Worksheet xSt;
            try
            {
                //WriteLog(logPath, "进入生成excel336");
                int excelColNum = dv.ToTable().Columns.Count;
                //if (excelColNum < 21)
                //    excelColNum = 21;
                xBk = excel.Workbooks.Add(true);
                //WriteLog(logPath, "进入生成excel341");
                xSt = (_Worksheet)xBk.ActiveSheet;
                xSt.Cells.NumberFormat = "@";
                //取消网格线               
                excel.ActiveWindow.DisplayGridlines = false;
                //设置显示比例
                excel.ActiveWindow.Zoom = 70;
                //设置字体
                #region 表头
                Range rg = xSt.Range[excel.Cells[1, 1], excel.Cells[999, 100]];
                rg.Font.Size = 14;
                rg.Font.Name = "Times New Roman";
                //设置行高
                rg.RowHeight = 33;
                //设置第2列宽
                rg = xSt.Range[excel.Cells[1, 2], excel.Cells[1, 2]];
                rg.ColumnWidth = 11.13;
                //第1行
                rg = xSt.Range[excel.Cells[1, 1], excel.Cells[1, 1]];
                rg.Select();
                //WriteLog(logPath, "进入生成excel361");
                float PicLeft, PicTop;
                PicLeft = Convert.ToSingle(rg.Left);
                PicTop = Convert.ToSingle(rg.Top);
                string picturePath = Server.MapPath("../") + @"Images\KAIFA.png";
                //WriteLog(logPath, picturePath);
                xSt.Shapes.AddPicture(picturePath, Microsoft.Office.Core.MsoTriState.msoFalse,
                    Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, 300, 70);

                //第2行
                excel.Cells[2, 7] = listSSHead[0].listSSD[1].ParameterValue;
                rg = xSt.Range[excel.Cells[2, 7], excel.Cells[2, 7]];
                rg.Font.Bold = true;
                rg.Font.Size = 20;
                rg.Font.Name = "宋体";

                ////第3行
                //excel.Cells[3, 13] = "IQC AUDIT RESULTS";
                //rg = xSt.Range[excel.Cells[3, 13], excel.Cells[3, 13]];
                //rg.Font.Size = 20;
                //rg.Font.Name = "宋体";
                ////第4行
                //excel.Cells[4, excelColNum - 1] = "Dept.: IQC/SQE";
                //rg = xSt.Range[excel.Cells[4, excelColNum - 1], excel.Cells[4, excelColNum - 1]];
                //rg.Font.Size = 20;
                //rg.Font.Name = "Times New Roman";

                //第5行
                //第6行
                //第7行
                //第8行
                //WriteLog(logPath, "进入生成excel392");
                rg = xSt.Range[excel.Cells[5, 1], excel.Cells[5, 8]];
                rg.Merge(0);
                //产品编号
                //WriteLog(logPath, "进入生成excel396");
                excel.Cells[5, 1] = listSSHead[0].listSSD[2].ParameterValue;
                rg = xSt.Range[excel.Cells[6, 1], excel.Cells[6, 8]];
                rg.Merge(0);
                //WriteLog(logPath, "进入生成excel400");
                //excel.Cells[6, 1] =ProductNo;
                excel.Cells[6, 1] = listSSHead[0].listSSD[0].ParameterValue + list[0].listSSD[0].ParameterValue;
                //文件版本
                //WriteLog(logPath, "进入生成excel404");
                WriteLog(logPath, listSSHead[0].listSSD.Count.ToString());
                WriteLog(logPath, list[0].listSSD.Count.ToString());

                excel.Cells[5, 9] = listSSHead[0].listSSD[3].ParameterValue;
                //excel.Cells[6, 5] = Version;
                excel.Cells[6, 9] = list[0].listSSD[2].ParameterValue;
                //产品名称
                //WriteLog(logPath, "进入生成excel409");
                excel.Cells[5, 14] = listSSHead[0].listSSD[4].ParameterValue;
                //excel.Cells[6, 14] = ProductName;
                excel.Cells[6, 14] = list[0].listSSD[3].ParameterValue;
                //工厂/用途/替代
                //WriteLog(logPath, "进入生成excel414");
                excel.Cells[5, 15] = listSSHead[0].listSSD[5].ParameterValue;
                excel.Cells[6, 15] = listSSHead[0].listSSD[6].ParameterValue;
                //产品描述
                //WriteLog(logPath, "进入生成excel418");
                excel.Cells[7, 1] = listSSHead[0].listSSD[7].ParameterValue;
                //excel.Cells[8, 1] = ProductDesc;
                excel.Cells[8, 1] = list[0].listSSD[1].ParameterValue;
                //客户名称
                //WriteLog(logPath, "进入生成excel423");
                excel.Cells[7, 14] = listSSHead[0].listSSD[8].ParameterValue;
                //excel.Cells[8, 14] = CustomerName;
                excel.Cells[8, 14] = listSSHead[0].listSSD[9].ParameterValue;
                //更新日期
                //WriteLog(logPath, "进入生成excel428");
                excel.Cells[7, 15] = listSSHead[0].listSSD[10].ParameterValue;
                //excel.Cells[8, 15] = getdate();
                excel.Cells[8, 15] = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                //WriteLog(logPath, "进入生成excel422");
                //注释
                #region
                //rg = xSt.Range[excel.Cells[5, 6], excel.Cells[5, 9]];
                //rg.Merge(0);
                //excel.Cells[5, 6] = "PART NAME";
                //rg = xSt.Range[excel.Cells[6, 6], excel.Cells[6, 9]];
                //rg.Merge(0);
                //excel.Cells[6, 1] = partNo;

                //rg = xSt.Range[excel.Cells[5, 10], excel.Cells[5, 12]];
                //rg.Merge(0);
                //excel.Cells[5, 10] = "VENDOR";
                //rg = xSt.Range[excel.Cells[6, 10], excel.Cells[6, 12]];
                //rg.Merge(0);

                //rg = xSt.Range[excel.Cells[5, 13], excel.Cells[5, 14]];
                //rg.Merge(0);
                //excel.Cells[5, 13] = "G.R.#";
                //rg = xSt.Range[excel.Cells[6, 13], excel.Cells[6, 14]];
                //rg.Merge(0);

                //rg = xSt.Range[excel.Cells[5, 15], excel.Cells[5, 16]];
                //rg.Merge(0);
                //excel.Cells[5, 15] = "LOT QTY";
                //rg = xSt.Range[excel.Cells[6, 15], excel.Cells[6, 16]];
                //rg.Merge(0);

                //rg = xSt.Range[excel.Cells[5, 17], excel.Cells[5, 18]];
                //rg.Merge(0);
                //excel.Cells[5, 17] = "INSPECTOR";
                //rg = xSt.Range[excel.Cells[6, 17], excel.Cells[6, 18]];
                //rg.Merge(0);

                //rg = xSt.Range[excel.Cells[5, 19], excel.Cells[5, 20]];
                //rg.Merge(0);
                //excel.Cells[5, 19] = "DATE";
                //rg = xSt.Range[excel.Cells[6, 19], excel.Cells[6, 20]];
                //rg.Merge(0);

                //rg = xSt.Range[excel.Cells[5, 21], excel.Cells[5, excelColNum]];
                //rg.Merge(0);
                //excel.Cells[5, 21] = "INSTRUMENT USED";
                //rg = xSt.Range[excel.Cells[6, 21], excel.Cells[6, excelColNum]];
                //rg.Merge(0);
                ////第7行
                //rg = xSt.Range[excel.Cells[7, 1], excel.Cells[7, excelColNum]];
                //rg.Merge(0);
                //excel.Cells[7, 1] = "TYPE OF AUDIT:(NORMAL/SKIP/MONTHLY)";
                ////第8行
                //rg = xSt.Range[excel.Cells[8, 1], excel.Cells[8, excelColNum]];
                //rg.Merge(0);
                #endregion

                ////第9行
                //excel.Cells[9, 1] = dv.Table.Columns["BOM料号"].ToString();
                //    //[0]["BOM料号"];
                //excel.Cells[9, 2] = dv.ToTable().Columns[0]["基本数量"];
                //excel.Cells[9, 3] = dv.ToTable().Columns[0]["BOM项目号"];
                //excel.Cells[9, 4] = dv.ToTable().Columns[0]["项目类别"];
                //excel.Cells[9, 5] = dv.ToTable().Columns[0]["替代策略"];
                //excel.Cells[9, 6] = dv.ToTable().Columns[0]["替代组"];
                //excel.Cells[9, 7] = dv.ToTable().Columns[0]["替代优先级"];
                //excel.Cells[9, 8] = dv.ToTable().Columns[0]["使用可能性按 %"];
                //excel.Cells[9, 9] = dv.ToTable().Columns[0]["BOM组件"];
                //excel.Cells[9, 10] = dv.ToTable().Columns[0]["组件数量"];
                //excel.Cells[9, 11] = dv.ToTable().Columns[0]["装配级数"];
                //excel.Cells[9, 12] = dv.ToTable().Columns[0]["位号"];
                //excel.Cells[9, 13] = dv.ToTable().Columns[0]["区域 (排序字符串)"];
                //excel.Cells[9, 14] = dv.ToTable().Columns[0]["英文物料描述"];
                //excel.Cells[9, 15] = dv.ToTable().Columns[0]["中文物料描述"];
                //excel.Cells[9, 16] = dv.ToTable().Columns[0]["制造商"];
                //excel.Cells[9, 17] = dv.ToTable().Columns[0]["制造商编号"];
                //excel.Cells[9, 18] = dv.ToTable().Columns[0]["发货地点"];
                //excel.Cells[9, 19] = dv.ToTable().Columns[0]["备注"];

                //第9行
                int rowIndex = 9;
                int colIndex = 0;
                foreach (DataColumn col in dv.Table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = col.ColumnName;
                    xSt.Range[excel.Cells[rowIndex, colIndex], excel.Cells
                    [rowIndex, colIndex]].HorizontalAlignment
                    = XlVAlign.xlVAlignCenter;//Set title format as middle 
                }
                //WriteLog(logPath, "进入生成excel5");

                //设置6-10行的对齐
                rg = xSt.Range[excel.Cells[5, 1], excel.Cells[9, excelColNum]];
                rg.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rg.VerticalAlignment = XlVAlign.xlVAlignBottom;
                rg = xSt.Range[excel.Cells[7, 1], excel.Cells[7, 1]];
                rg.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                #endregion

                //注释
                #region
                //第10行
                //int rowIndex = 10;
                //int colIndex = 0;
                //foreach (DataColumn col in dv.Table.Columns)
                //{
                //    colIndex++;
                //    if (colIndex == 1)
                //    {
                //        excel.Cells[10, colIndex] = "#";
                //        continue;
                //    }
                //    if (colIndex == 2)
                //    {
                //        excel.Cells[10, colIndex] = "";
                //        continue;
                //    }
                //    excel.Cells[10, colIndex] = col.ColumnName;
                //    xSt.Range[excel.Cells[10, colIndex], excel.Cells
                //    [10, colIndex]].HorizontalAlignment
                //    = XlVAlign.xlVAlignCenter;//Set title format as middle 
                //}
                #endregion

                #region 内容

                //数据部分
                //数据部分单元格一起填充
                rg = xSt.Range[excel.Cells[10, 1], excel.Cells[9 + dv.Count, dv.Table.Columns.Count]];
                //rg.NumberFormatLocal = "0.00000_ ";
                object[,] objData = new Object[dv.Count, dv.Table.Columns.Count];
                IList<Limit> listLt = new List<Limit>();
                IList<Limit> listNS = new List<Limit>();
                IList<int> listNotStandard = new List<int>();
                //WriteLog(logPath, "进入生成excel554");
                for (int i = 0; i < dv.Count; i++)
                {
                    for (int j = 0; j < dv.ToTable().Columns.Count; j++)
                    {
                        objData[i, j] = dv[i][j];
                        #region 记录在范围之外的值

                            try
                            {
                                if (!listNotStandard.Contains(10 + i))
                                    listNotStandard.Add(10 + i);
                            }
                            catch { }

                        #endregion
                    }
                }
                rg.Value2 = objData;
                //  WriteLog(logPath, "进入生成excel7");
                //foreach (Limit lt in listLt) //变土黄
                //{
                //    rg = xSt.Range[excel.Cells[lt.Row, lt.Column], excel.Cells[lt.Row, lt.Column]];
                //    //设置单元格背景颜色
                //    rg.Cells.Interior.Pattern = Constants.xlSolid;
                //    rg.Cells.Interior.PatternColorIndex = Constants.xlAutomatic;
                //    rg.Cells.Interior.Color = 3381759;
                //    rg.Cells.Interior.TintAndShade = 0;
                //    rg.Cells.Interior.PatternTintAndShade = 0;
                //}
                foreach (int row in listNotStandard)//变绿
                {
                    rg = xSt.Range[excel.Cells[row, 1], excel.Cells[row, dv.Table.Columns.Count]];
                    //设置单元格背景颜色
                    rg.Cells.Interior.Pattern = Constants.xlSolid;
                    rg.Cells.Interior.PatternColorIndex = Constants.xlAutomatic;
                    rg.Cells.Interior.Color = 10092441;
                    rg.Cells.Interior.TintAndShade = 0;
                    rg.Cells.Interior.PatternTintAndShade = 0;
                }
                //oSheet.Cells.Columns.AutoFit();
                #endregion
                //设置5-内容行的边框
                rg = xSt.Range[excel.Cells[5, 1], excel.Cells[9 + dv.Count, excelColNum]];
                rg.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                // 
                //footer
                //
                //Form No
                //WriteLog(logPath, "进入生成excel603");
                excel.Cells[10 + dv.Count, 1] = listSSHead[0].listSSD[11].ParameterValue;
                //   WriteLog(logPath, "进入生成excel8");
                System.IO.FileInfo fi = new System.IO.FileInfo(FullFileName);
                if (!System.IO.Directory.Exists(fi.DirectoryName))
                    System.IO.Directory.CreateDirectory(fi.DirectoryName);
                //   WriteLog(logPath, "进入生成excel9");

                if (System.IO.File.Exists(FullFileName))
                {
                    File.Delete(FullFileName);
                }
                //  WriteLog(logPath, "进入生成excel10");
                xBk.SaveCopyAs(FullFileName);
                dv = null;
                xBk.Close(false, null, null);
                excel.Quit();
                // Marshal.ReleaseComObject(excel);
                //WriteLog(logPath, "进入生成excel621");

            }
            finally
            {
                //rg = null;
                //xSt = null;
                //xBk = null;
                //excel = null;
                try
                {
                    Kill_Excel(excel);
                }
                catch { ; }
                GC.Collect();
            }
            #endregion
            //WriteLog(logPath, "成功生成excel638");
            #region download excel
            DownLoadFile(FullFileName, Response, Server, logPath);

            //WriteLog(logPath, "成功下载excel");
            #endregion
        }

        public static void DownLoadFile(string FullFileName, HttpResponse Response, HttpServerUtility Server, string logPath = "")
        {
            //WriteLog(logPath, "DownLoadFile 1");
            System.IO.FileInfo file = new System.IO.FileInfo(FullFileName);
            Response.Clear();
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            //Add header, give a default file name for "File Download/Store as"
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
            //Add header, set file size to enable browser display download progress
            Response.AddHeader("Content-Length", file.Length.ToString());
            //Set the return string is unavailable reading for client, and must be downloaded
            Response.ContentType = "application/ms-excel";
            //WriteLog(logPath, "DownLoadFile 2");
            //Send file string to client 
            Response.WriteFile(file.FullName);
            //WriteLog(logPath, "DownLoadFile 3");

            //Stop execute  
            HttpContext.Current.ApplicationInstance.CompleteRequest();
           // WriteLog(logPath, "DownLoadFile 4");

        }


        private void ExporttoHtml(System.Data.DataTable dt, string FileName)
        {
            System.IO.StringWriter stringWriter = new System.IO.StringWriter();
            HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
            DataGrid excel = new DataGrid();
            System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
            headerStyle.BackColor = System.Drawing.Color.LightGray;
            headerStyle.Font.Bold = true;
            headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
            excel.HeaderStyle.MergeWith(headerStyle);
            excel.GridLines = GridLines.Both;
            excel.HeaderStyle.Font.Bold = true;
            excel.DataSource = dt; //输出DataTable的内容 
            excel.DataBind();
            excel.RenderControl(htmlWriter);
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            HttpContext.Current.Response.ContentType = ".xls";
            HttpContext.Current.Response.Write(stringWriter.ToString());
            HttpContext.Current.Response.End();
        }

        public static System.Data.DataTable ImportExcel(string p1, out int count_2, int p2)
        {
            throw new NotImplementedException();
        }

        public static void OutputTxt(string p1, string p2, HttpResponse httpResponse, HttpServerUtility Server)
        {
            throw new NotImplementedException();
        }
    }

    public class Limit
    {
        public int Row { get; set; }
        public int Column { get; set; }
    }
}
